home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
The PC-SIG Library 10
/
The PC-Sig Library - Shareware for the IBM PC and Compatibles (PC-SIG)(Tenth Edition Disks 1-2804)(1991).iso
/
PC_SIGCD
/
03
/
8
/
DISK0383.ZIP
/
MANUAL.2
< prev
next >
Wrap
Text File
|
1985-08-14
|
56KB
|
2,510 lines
CHAPTER 5
COMMAND REFERENCE
OVERVIEW
The commands summarized in Table 5-1 are implemented in
PC-DBMS. Subsequent sections document them.
COMMAND FUNCTION
DEFINE invokes define utility to update schema
DEKEY removes an index
DISPLAY invokes display utility to update
a table
EXIT terminates execution of PC-DBMS
KEY builds an index
OPEN makes available an existing or new
data base
OUTPUT redirects output to file or printer
REMOVE removes table from the data base
SELECT retrieves records from one or two tables
SHOW displays portion of schema
TOLERANCE sets tolerance used for numeric
comparisons
Table 5-1. Summary of PC-DBMS commands.
5-1
COMMAND REFERENCE
NOTATIONAL CONVENTIONS
NOTATIONAL CONVENTIONS
Syntax forms for the commands use the following
notational conventions.
- Lower case words indicate that you should
substitute a word or value of your choice.
- Upper case words are keywords and they should be
used where shown. However, keywords can be
truncated to three or more characters and can be
entered in upper or lower case.
- Brackets ([]) indicate optional elements.
- Ellipses (...) indicate the preceding item(s)
may be repeated one or more times.
DEFINE
The define command is issued after the open command is
given for a new or existing data base. It invokes the
define utility to enter definitions of tables and their
fields. The syntax is:
DEFINE
The define utility is conversational. It presents two
data entry screens. The first screen accepts the
description of a table. The second screen accepts
descriptions of that table's fields.
The first or table screen prompts for the table's name,
the specification of the file containing it, the record
termination character and an optional verbal description.
5-2
COMMAND REFERENCE
DEFINE
When that screen is completed, the second screen type
appears for entry of field data -- field name, data type,
field width, indexing and an optional verbal description.
When completed, the screen reappears blank for definition
of the next field. If there is no "next" field, press
F10 and the first data entry screen will reappear to
define the next table. Then, if there is no "next"
table, press F10 to exit the define utility.
File specifications may not include path names but drive
names may be used. If a drive name is given, the file
must always be on that disk drive. If no drive name is
given, the file must be on the logged (default) drive
which you can set at the DOS level.
The table screen prompts for a number to describe the end
of record terminator. Enter 0 for no terminator, 1 for
carriage return and 2 for carriage return-line feed. The
choice of 2 facilitates processing the file outside
PC-DBMS.
Fields may have one of three data types: text, number or
filler. Filler fields may contain anything -- blanks,
numbers or text. The indexing and description
information is not solicited for filler fields. Filler
fields can be displayed with the select command but may
not be used in where or sort clauses. The display
utility ignores them; on input, they are blank filled.
Filler fields are useful in masking columns of extraneous
material in imported files or to insert blanks between
fields during input.
If a field is more than 141 characters wide, it cannot be
indexed so the index prompt will not appear.
If the data base is to include a table in an imported
file, its definition must exactly describe the file.
Filler fields of any desired width can be used. Any
record whose first character is a backslash (\) will be
considered deleted.
5-3
COMMAND REFERENCE
DEKEY
DEKEY
The dekey command updates the schema so that it shows the
named field as not keyed. It has no affect on the index
file but the old index will never be usable. The syntax
is:
DEKEY qfn
Qfn is a qualified field name. For example, DEKEY
OPUS.WORK changes the schema so that field WORK in table
OPUS is regarded as not keyed. When a field is indexed,
it is necessary to maintain the indices when records are
added, changed or deleted. Dekeying avoids this
overhead.
DISPLAY
The display command projects a data entry form on the
screen. The form consists of background text and field
blocks corresponding to non-filler fields in the data
table. The syntax is:
DISPLAY [\INPUT] table_name [WHERE ]
\QUERY
\EDIT
The display form operates on a single table given by
table_name. That table may have up to 60 fields. Some
tables may be rejected because all their non-filler
fields cannot be squeezed onto the screen.
A form will be created dynamically. Display fields
corresponding to data fields appear as rectangular
blocks; they are one or more characters wide and one or
more lines high.
5-4
COMMAND REFERENCE
DISPLAY
The display command has four forms. With the \QUERY
qualifier, a select command is composed for use as the
next command. If \INPUT is specified, new records are
entered and appended to the table. The default \EDIT
qualifier permits retrieval, editing and deletion of
existing records. The where clause form of the command
also retrieves records for editing and deletion, those
satisfying the where clause. Screen layouts are similar
for all forms; only the bottom information lines differ.
The various forms are discussed below.
An example of the \INPUT form is
DISPLAY\INPUT COMP
In this example, a form will be projected with a blank
field for each non-filler data field in the COMP table.
Next to each field, its name will be shown. At the
bottom right of the screen, INPUT will be displayed to
identify the function. The rest of the bottom line gives
other useful information.
Fill in all fields (or leave some blank) and press F7 to
store the new record. The form reappears with blank
fields to enter another record. Press F10 to exit the
display utility. However, exiting does not store any
record still on screen.
The \QUERY form of the display command helps compose
certain select commands. The form shows QUERY at the
bottom right and has blank fields. Fill in up to ten of
them and press F7. This exits the display utility and
restores the main screen. A select command resulting
from the QUERY form entries will be in the command
window. Edit, erase or execute it. It will have AND
connectives, no parentheses, no negations, = comparisons
and ALL in its display list.
For example, suppose you invoke the display utility with
DISPLAY\QUERY COMP
5-5
COMMAND REFERENCE
DISPLAY
You enter 1770 in the BORN field and Beethoven, L. in
the NAME field. After pressing F7, the main screen
reappears with the following in the command window:
SELECT COMP.ALL WHERE NAME="Beethoven, L." AND
BORN=1770
The command can be edited if it's unsuitable. For
example, only certain fields might be desired in the
output.
The \EDIT form of the display command is used to find and
edit or delete records. Two phases repeat in a cycle.
First, a screen query is executed. Second, qualifying
records are displayed one after the other on the form for
editing and deletion. When the editing and deletion are
complete, the blank query screen reappears to repeat the
process.
For example, to edit records in the COMP table, the
command is
DISPLAY\EDIT COMP
The query phase of the cycle is almost identical to the
DISPLAY\QUERY function. A form appears with QUERY at the
bottom right and blank fields. Fill in up to ten fields
and press F7 to execute the query. Alternatively, press
F10 to exit from the display utility.
If F7 was pressed, all the qualifying records are
displayed one at a time on the form. The bottom line
will now show editing prompts and EDIT at the right.
As each qualifying record is displayed, do one of three
things: leave it unchanged, edit it or delete it. To
leave it unchanged, press F7. To edit it, make the
desired changes on screen and press F6. To delete it,
press F4. In any case, the next qualifying record will
be displayed. However, to return to the first (query)
phase, press F10. It is not possible to exit the display
5-6
COMMAND REFERENCE
DISPLAY
utility directly from the edit screen.
After all qualifying records have been displayed, the
screen will clear and the following message will appear:
NO (MORE) RECORDS QUALIFY
Press any key to continue
After pressing a key, the query screen reappears to begin
the query-edit cycle anew. Alternatively, press F10 to
exit the display utility.
DISPLAY\EDIT is the default. That is, if there is no
where clause and no qualifier (\), then the \EDIT
qualifier is assumed.
The last form of the display command is the only one that
uses the where clause. Any qualifier is ignored. This
form of the command retrieves for editing and deletion
records which satisfy the where clause.
The where clause is documented under the select command.
The default table name at the beginning of the clause is
table_name. A second table can be referenced in the
clause.
Qualifying records are projected on the same edit screen
used with the \EDIT form of the command. However, after
the last record has been processed, the display utility
is exited.
For example, one way to retrieve Beethoven's record for
editing is with this command:
DISPLAY COMP WHERE NAME $ "Beethoven"
When text fields are entered or displayed on screen
forms, quote marks are treated as ordinary characters;
they are not string delimiters. Hence, use one quote for
each quote mark you wish to embed in the record.
5-7
COMMAND REFERENCE
DISPLAY
Numbers are entered according to the rules for numbers in
commands. See COMMAND FORMATTING AND EXECUTION in
chapter 4.
The PC-DBMS editor operates in each field block.
However, only replace mode is available. See THE PC-DBMS
EDITOR in chapter 4.
To move the cursor forwards from one field to the next,
press tab. Press F9 to move it backwards.
A help screen is available by pressing F1.
Whenever records are added, deleted or modified by the
display utility, the corresponding index file is
automatically updated. However, if more than 13 fields
are indexed, only the first 13 will be updated. The
indices on the other fields will be incorrect. They
should be dekeyed and then rebuilt with the key command.
In practice, few tables have more than 13 keys.
EXIT
The exit command terminates execution of PC-DBMS and
returns the user to the operating system. The command's
syntax is EXIT but it can be issued by pressing F10.
KEY
The key command builds an index for the specified field
and updates the schema to reflect that fact. The syntax
is:
KEY qfn
5-8
COMMAND REFERENCE
KEY
Qfn is a qualified field name. For example, KEY
OPUS.WORK builds an index for the field WORK in the table
OPUS. The index will be updated when the table is
altered by the display command.
Indexing will be aborted and an error message displayed
if an attempt is made to key a field wider than 141
characters.
OPEN
Open is typically the first command the user gives. It
makes an existing data base available. If the data base
does not exist, its schema file will be created. The
command syntax is:
OPEN dbname
Dbname is the name of the existing or new data base. For
example, OPEN MUSIC.
There is no close command. An open data base will be
closed when the exit or open command is given.
OUTPUT
The output command is used to redirect the output of the
show and select commands to a file or to a device such as
a printer. The syntax is:
OUTPUT [file-spec]
File-spec is the name of the file or device. For
example, OUTPUT FILE.DAT sends output to a disk file and
OUTPUT LPT1: prints the output. If no file
specification is given, the screen default will be
restored.
5-9
COMMAND REFERENCE
OUTPUT
If file-spec is a disk file which already exists, it will
be deleted and a new file of the same name created.
The redirected output will include column headings. A
file created by this command will not become part of the
data base. That function is served by the "into" clause
of the select command.
REMOVE
The remove command marks as deleted the schema definition
of the named table(s). The syntax is:
REMOVE table_name [...]
For example, REMOVE CUSTOMERS BALANCES removes the two
named tables. The data files are unaffected by this
command but they can no longer be accessed.
SELECT
The select command retrieves data from the tables. It is
capable of gathering data from one or two tables,
filtering and sorting the retrieved records, displaying
up to 12 of their fields in any order, and writing the
result to a new table which becomes a part of the data
base. The output ordinarily is written to the bottom
half of the screen or whatever target is specified by the
output command. However, output will be written to the
designated new table if the into clause is used.
The abbreviated syntax is:
SELECT qfn's [WHERE ] [SORTED BY ]
[INTO new_table_name]
5-10
COMMAND REFERENCE
SELECT
The first clause, qfn's, is the only necessary one. It
is a list of qualified field names to be displayed. The
fields can come from one or two tables. An example of a
two table retrieval is SELECT COMP.NAME COMP.BORN
OPUS.WORK. If more than twelve fields are specified,
additional ones will be ignored and the query will be
executed. The display will be truncated to accommodate
screen width.
To display all fields of a table, two special qfn's can
be used. The table name and period are as usual but the
field name is either ALL or ALLF. For example, SELECT
COMP.ALL displays all non-filler fields of the COMP
table. ALLF is used when the filler fields are also
desired.
Each time a table name is encountered (in a qfn), that
table becomes the default. Subsequent field names which
omit the table name and period reference the default
table. For example, SELECT COMP.NAME COUNTRY OPUS.ALL
displays two fields from COMP and all fields from OPUS.
Figure 5-1 is an example of a simple select command.
5-11
COMMAND REFERENCE
SELECT
SELECT COMP.ALL
NAME BORN DIED COUNTRY
Tchaikovsky, P. 1840 1893 Russia
Brahms, J. 1833 1897 Germany
Verdi, G. 1813 1901 Italy
Handel, G. 1685 1759 England
Mozart, W. 1756 1791 Austria
Bach, J. 1685 1750 Germany
Chopin, F. 1810 1849 France
Beethoven, L. 1770 1827 Germany
Haydn, F. 1732 1809 Austria
Mendelssohn, F. 1809 1847 Germany
Debussy, C. 1862 1918 France
Figure 5-1. A simple select command.
The where clause is optional. A where clause contains
one to ten comparisons (conditions) connected by ANDs and
ORs. A record must satisfy the where clause to be
displayed. For example, attaching the where clause WHERE
BORN>1799 to the preceding query would eliminate from the
display the five composers born before 1800.
The eight comparisons are shown in Table 5-2.
COMPARISON REPRESENTATION
less than <
less than or equal to <=
equal to =
greater than or equal to >=
greater than >
unequal to <>
contains $
does not contain %
Table 5-2. The eight comparison operators.
5-12
COMMAND REFERENCE
SELECT
On one side of the comparison there must be a field name,
possibly qualified. On the other side, there may be
another field name, possibly qualified, or a constant,
numeric or text as appropriate. The elements on the two
sides of the comparison must be of the same data type,
both numeric or both text. Filler fields may not be used
in comparisons. All eight operators can be used with
text elements. Only the first six can be used with
numeric elements. Spaces before and after the operator
are optional.
For numeric comparisons, the default tolerance is zero.
The tolerance can be altered with the tolerance command.
Text constants used with the first six comparison
operators are right padded with blanks to match the
length of the field element on the other side of the
comparison. If a text constant is longer than the field
element, the query will be rejected. Text comparisons
use the ASCII collating sequence. Hence, WHERE NAME>"B"
excludes NAMEs beginning with "A".
The $ and % operators test whether the left hand element
contains the right hand element. For example, NAME $
"Bee" is true for the Beethoven record but false for the
other composers. The % operator returns the opposite
result.
The default table name in the where clause is inherited
from the qfn list but is changed each time a qualified
field name is encountered.
Fields from different tables may appear in the same
comparison. If qfn's from different tables are in the
display list, it is likely there will be at least one
cross-table comparison. For example, Figure 5-2 shows a
join of composers (COMP) with their works (OPUS).
5-13
COMMAND REFERENCE
SELECT
SELECT COMP.ALL OPUS.WORK WHERE COMPOSER=COMP.NAME
NAME BORN DIED COUNTRY WORK
Brahms, J. 1833 1897 Germany Lullaby
Beethoven, L. 1770 1827 Germany Symphony No. 5
Figure 5-2. Using select for a join.
The 11 records in COMP are paired with the 2 in OPUS for
22 possible combinations. Of those 22, only the two
which have identical values of COMP.NAME and
OPUS.COMPOSER are displayed. (This conceptual execution
of a join is not necessarily how PC-DBMS implements it.)
PC-DBMS permits up to ten comparisons to be used, linked
together by ANDs and ORs. For example, to find composers
who lived entirely in the nineteenth century, use
SELECT COMP.ALL WHE BORN>1799 AND DIED<1900
The complement to this set of composers is the set of
composers not alive at any time during the nineteenth
century. One way to find them is with the complement
operator, NOT. For example,
SELECT COMP.ALL WHE NOT ( BORN>1799 AND DIED<1900 )
NOT makes anything that is true false and vice versa. It
is typically used on a combination of comparisons as
above because the individual comparisons have their own
complements. For example, NOT NAME>"Smith" is just
NAME<= "Smith". Space between NOT and ( is optional.
Parentheses, nested to any desired depth, can be used to
alter the order of evaluation of a where clause. In the
preceding comparison, parentheses assure that the two
comparisons are evaluated and combined and then that
result is negated.
5-14
COMMAND REFERENCE
SELECT
Like algebra, there is an order of evaluation in where
clauses. Comparisons are evaluated (to true and false)
first. Then NOTs are applied. AND comes next and OR is
last.
A table may be referenced for the first time in the
command in the where clause.
The sorted by clause is also optional. It contains a
list of one to five field names from tables referenced
earlier in the command. The first field name is the
major sort key and the last is the minor sort key.
Filler fields may not be used.
The default sorting order is ascending (aardvarks before
zebras). To get descending order on a key, follow it
with "\d" or "\D". Use "\a" or "\A" to get ascending
order, the default.
For example, Figure 5-3 shows retrieval of composers in
reverse order of birth and break ties (Bach and Handel)
by alphabetizing their names.
SEL COMP.ALL SOR BY BORN\D NAME
NAME BORN DIED COUNTRY
Debussy, C. 1862 1918 France
Tchaikovsky, P. 1840 1893 Russia
Brahms, J. 1833 1897 Germany
Verdi, G. 1813 1901 Italy
Chopin, F. 1810 1849 France
Mendelssohn, F. 1809 1847 Germany
Beethoven, L. 1770 1827 Germany
Mozart, W. 1756 1791 Austria
Haydn, F. 1732 1809 Austria
Bach, J. 1685 1750 Germany
Handel, G. 1685 1759 England
Figure 5-3. A select command with sorting.
5-15
COMMAND REFERENCE
SELECT
The select command will abort with an error message if
the output string is too wide. The output string
contains the fields to be displayed plus two spaces
between adjacent fields. Spaces are suppressed if the
into clause is present. Add to the output string width
the width of the widest sort field. The sum may not
exceed 141.
The optional into clause comes last. It makes the
retrieved table part of the data base by writing the
output to a file and making appropriate entries in the
schema. Only an information message will be displayed on
screen.
Into is followed by the name for the new table. That
name follows the conventions for naming tables but may
not exceed eight characters. The name may not already be
in use as a table name. The file for the table will have
the table's name as its file name and DAT as its
extension. It will be in the current directory of the
logged drive. No drive will be recorded for it in the
schema. The new table will have no indices.
No more than two tables can be referenced prior to the
into clause.
An example of a complex select command follows. It
illustrates simultaneous use of all the clauses.
SELECT COMP.COUNTRY COMP.BORN OPUS.ALL
WHERE COMPOSER=COMP.NAME
AND NOT (DIED<1900 AND BORN>1799)
SORT BY NAME BORN\D
INTO NU_TABLE
During output, scrolling can be suspended by pressing the
control-num lock or control-S key and resumed by pressing
any other key. The F10 key aborts output.
5-16
COMMAND REFERENCE
SELECT
Skeletal select commands can be composed easily with the
DISPLAY\QUERY command.
SHOW
The show command displays a portion of the schema. The
syntax is:
SHOW [\FIELDS] [\DESCRIBE] [table_name ...]
The optional qualifiers \FIELDS and \DESCRIBE determine
what kind of information is retrieved from the schema.
It is possible to use zero, one or two qualifiers. Note
that SHOW\FIELDS\DESCRIBE has the same effect as
SHOW\DESCRIBE\FIELDS.
The FIELDS qualifier displays information on fields.
Without this qualifier, information on tables is
retrieved. The DESCRIBE qualifier retrieves verbal
descriptions. Without this qualifier, tabular
information is retrieved.
If one or more tables is named, only information on those
tables is retrieved. Otherwise, data on all tables is
shown.
Four examples follow. They show all possible
combinations of the qualifiers (one, the other, both,
neither). For brevity, schema information is retrieved
for only one table.
The unqualified show command produces tabular information
on one or more tables. See Figure 5-4.
5-17
COMMAND REFERENCE
SHOW
SHOW COMP 'info on table COMP
TABLE NAME FILE RECORDS FIELDS EOR MODIFIED
COMP COMPOSER.DAT 11 6 2 01-11-85
Figure 5-4. The show command without qualifiers.
The output exhibits the number of records and fields.
EOR gives the end of record terminator; 0 means no
terminator, 1 means carriage return and 2 means carriage
return-line feed.
With just the DESCRIBE qualifier, show displays a verbal
description of one or more tables. See Figure 5-5.
SHO\DESCRIBE COMP 'description of table COMP
TABLE NAME DESCRIPTION
COMP information about some composers
Figure 5-5. The show\describe command.
With just the FIELDS qualifier, show displays tabular
information about the fields of one or more tables. See
Figure 5-6.
5-18
COMMAND REFERENCE
SHOW
SHO\FIELDS COMP 'info on fields in table COMP
FIELD NAME TYPE KEY FROM TO
TABLE COMP
NAME T N 1 19
BORN N N 20 23
filler1 F N 24 24
DIED N N 25 28
filler2 F N 29 29
COUNTRY T N 30 44
Figure 5-6. The show\fields command.
The TYPE column indicates the field's data type: T for
text, N for numeric and F for filler. KEY indicates if
the field is indexed: Y for yes and N for no. The FROM
and TO columns show each field's starting and ending
positions in the data file.
Show with both qualifiers (either order) exhibits verbal
descriptions of the fields of one or more tables. See
Figure 5-7.
5-19
COMMAND REFERENCE
SHOW
SHO\FIE\DES COMP 'descriptions of fields in COMP
FIELD NAME DESCRIPTION
TABLE COMP
NAME the composer's last name and first initial
BORN the year the composer was born
filler1
DIED the year of the composer's death
filler2
COUNTRY the composer's country of residence
Figure 5-7. The show\fie\des command.
During output, scrolling can be suspended by pressing the
control-num lock or control-S key and resumed by pressing
any other key. The F10 key aborts output.
TOLERANCE
The tolerance command sets the comparison tolerance used
in processing where clauses. It affects only numerical
comparisons. The syntax is
TOLERANCE number
The tolerance is set to the absolute value of number.
The default value is zero, i.e., the value before this
command is first given.
Slight conversion errors occur in converting between
decimal (used by people and stored in files) and binary
(used internally by PC-DBMS) because the machine does not
have infinite precision. Non-zero tolerance circumvents
these errors.
5-20
COMMAND REFERENCE
TOLERANCE
The range created by tolerance can be exploited to
simplify queries. For example, with zero tolerance,
certain records are qualified by
WHERE YEAR>=1895 AND YEAR<=1905
If tolerance is set to 5, the same records will, ignoring
conversion errors, be qualified by
WHERE YEAR=1900
Here are the formal tolerance rules. If F is a field
with value a and r is another field or a user specified
number and t is the tolerance, then the following are
true conditions.
F = r if and only if r-t <= a <= r+t
F <> r if and only if a < r-t or a > r+t
F > r if and only if a > r-t
F >= r if and only if a >= r-t
F < r if and only if a < r+t
F <= r if and only if a <= r+t
If NOT precedes a where clause condition, the operator of
the condition will be complemented and the NOT removed
when the condition is processed. For example, NOT YEAR >
1900 is processed as YEAR <= 1900. If tolerance is 2,
then records with YEAR greater than 1898 should be
excluded. In fact, records with YEAR less than or equal
to 1902 will be included.
5-21
APPENDIX A
KEYWORDS
PC-DBMS keywords can be truncated to three or more
characters and entered in upper, lower or mixed case.
The keywords and their truncations should not be used for
names of data bases, tables or fields. A list of
keywords is in Table A-1.
ALL INTO
ALLF KEY
AND OPEN
BY OR
DEFINE OUTPUT
DEKEY QUERY
DESCRIBE REMOVE
DISPLAY SELECT
EDIT SHOW
EXIT SORTED
FIELDS TOLERANCE
INPUT WHERE
Table A-1. The keywords of PC-DBMS.
A-1
APPENDIX B
THE RELATIONAL MODEL
PC-DBMS is a data base management system which implements
the relational model of data management. This appendix
describes the relational model and how to design data
bases around it.
Relational data bases contain data tables. These are
familiar objects; they have rows called records and
columns called fields.
Data bases typically have more than one table. For a
given information content, a data base might have one,
two, three or more tables. How many tables should
contain that information? Which fields should be put
into which tables? The purpose of this appendix is to
help you answer those questions, i.e., to help you design
your data base. If you're still looking for more
information when you finish, you might consult C. J.
Date, "An Introduction to Database Systems," (Reading,
Massachusetts: Addison-Wesley Publishing Co., 1976).
We will progress through several data bases, each better
organized than the last.
First, consider the one table data base in Figure B-1.
B-1
THE RELATIONAL MODEL
NAME_1 SSNO_1 NAME_2 SSNO_2
Jack 123-45-6789 Mary 987-65-4321
Figure B-1. A table with repeating fields.
There are four fields and one record. It is obvious that
the fields repeat; the second pair of fields contain the
same sort of information as the first pair. This kind of
table is called unnormalized.
Suppose we have to find Mary's Social Security number.
We would have to search the NAME_1 field and the NAME_2
field for "Mary". She might be in either one. This is a
difficult query. More difficult still is a join, that
is, connecting the records in this table with the records
in another table on the basis of a common field like
Social Security number. Each record has two such
numbers. Which one should be used?
Unnormalized tables can be normalized simply. Just make
additional records of the repeating fields as in Figure
B-2.
NAME SSNO
Jack 123-45-6789
Mary 987-65-4321
Figure B-2. A normalized table.
Normalized tables are said to be in first normal form
(1NF).
Let's define a concept known as the primary key of a
table. The primary key is a field or combination of
fields which, when their values are specified, uniquely
identifies no more than one record. For example, in a
table with the two fields SSNO and BIRTH_PLACE, SSNO
would be the primary key because for each listed Social
B-2
THE RELATIONAL MODEL
Security number there is only one birth place.
BIRTH_PLACE cannot be the primary key because for a given
BIRTH_PLACE there may be many Social Security numbers
(people).
If a table has a primary key, its records are unique.
It may take several fields to comprise a primary key.
For example, in a table which gives different people's
incomes for different years, there are the fields SSNO,
YEAR and INCOME. The first two uniquely identify the
record; they are the primary key.
Let's append a fourth field to this two field primary key
table to study second normal form (2NF). The table is
already in 1NF because its records do not contain lists
or repeating fields. See Figure B-3.
SSNO YEAR INCOME NAME
123-45-6789 1980 15000 Jack
987-65-4321 1980 22000 Mary
123-45-6789 1981 16500 Jack
987-65-4321 1981 24000 Mary
Figure B-3. A table not in 2NF.
Once both SSNO and YEAR are specified, the record can be
identified and the other two fields looked up. Neither
field in the primary key is sufficient to identify INCOME
uniquely. For example, YEAR = 1980 identifies two
records. The entire primary key, both fields, is
necessary to identify INCOME.
However, once SSNO is known, NAME can be looked up in
Figure B-3. That is, NAME depends on less than all the
fields in the primary key. In relational jargon, INCOME
is fully functionally dependent on the primary key but
NAME is only functionally dependent.
B-3
THE RELATIONAL MODEL
If all the non-key fields in a 1NF table are fully
functionally dependent on the primary key, the table is
in 2NF. Why should we care?
Data base corruption is one reason we should care. For
example, suppose in entering "Mary" twice, it was
misspelled once as "Marie". Then, one Social Security
number would correspond to two NAMEs. Note that full
functional dependency implies only one value per non-key
field (INCOME) for each primary key value. Incorrect
replicates (Mary and Marie) cannot exist because multiple
entries per primary key are precluded. Hence,
consistency or integrity is one reason for 2NF.
There are other reasons for preferring 2NF but let's go
on to correct the problem. Split one table into two.
See Figure B-4.
SSNO YEAR INCOME SSNO NAME
123-45-6789 1980 15000 123-45-6789 Jack
987-65-4321 1980 22000 987-65-4321 Mary
123-45-6789 1981 16500
987-65-4321 1981 24000
Figure B-4. Tables in 2NF.
Both tables are in 2NF. In the left table, SSNO and YEAR
are the primary key and INCOME is fully functionally
dependent on them. In the right table SSNO is the
primary key and NAME is the non-key field (but full
functional dependency is immaterial because of the one
field primary key).
Note that in the two table version of the data base, we
can record Fred's Social Security number (right table)
although there isn't enough information to make an entry
for him in the left table. This capability did not exist
in the single table (1NF) version.
B-4
THE RELATIONAL MODEL
The table in Figure B-5 is in 2NF but it still has
problems.
SSNO AGE HOME_TOWN AVG_TEMP
123-45-6789 32 Boston 53
987-65-4321 19 Dallas 59
369-47-5814 50 El Paso 59
421-63-9631 41 Boston 47
Figure B-5. A table not in 3NF.
SSNO is the primary key of this table. The non-key
fields, AGE, HOME_TOWN and AVG_TEMP, are functionally
dependent on the primary key. (They are fully
functionally dependent, too, but this is trivial for a
one field primary key.)
Observe that Boston has two average temperatures and they
are different. True, this illustrates a data entry
error. However, it is possible to structure a data base
to guard against such errors. That's what third normal
form (3NF) is for.
In Figure B-5, there is a functional dependency between
non-key fields. That is, AVG_TEMP is dependent on
HOME_TOWN. Once HOME_TOWN is known, an uncorrupted data
base would tell us AVG_TEMP. Dependency among non-key
fields is permissible in 2NF but not in 3NF.
The remedy, as before, is to split one table into two.
See Figure B-6.
SSNO AGE HOME_TOWN HOME_TOWN AVG_TEMP
123-45-6789 32 Boston Boston 53
987-65-4321 19 Dallas Dallas 59
369-47-5814 50 El Paso El Paso 59
421-63-9631 41 Boston
Figure B-6. Tables in 3NF.
B-5
THE RELATIONAL MODEL
In each new table of Figure B-6, the left field is the
primary key. In the left table, the two non-key fields
are mutually independent -- knowing either does not
identify the other. Hence, the left table is in 3NF.
There is only one non-key field in the right table so 2NF
implies 3NF.
Aside from avoiding the corruption encountered earlier,
it is also possible to record the AVG_TEMP for a
HOME_TOWN even if we don't know the SSNO of anyone who
lives there. That is, we can have a record in the right
table without a correspondent in the left table.
Similarly, we can have a record in the left table even if
we don't know the AVG_TEMP of that person's HOME_TOWN.
To sum up, a table in 1NF has no lists or repeating
fields in its records. A 2NF table is a 1NF table in
which each non-key field is fully functionally dependent
on the entire primary key. Finally, a 3NF table is a 2NF
table in which the non-key fields are mutually
independent.
A data base consisting of third normal form tables
provides the most protection against corruption. It also
permits tables to be modified individually, e.g., we
don't have to know the age and Social Security of someone
in Detroit to enter that city's average temperature in
Figure B-6.
B-6
APPENDIX C
USER SUPPORTED SOFTWARE
PC-DBMS is distributed as user supported software. You
are encouraged to give copies of the distribution
diskette to others. There is no license agreement but
please pass along all files without modification. User
groups and similar organizations may impose a nominal
charge to cover the cost of duplication.
The user supported concept of software is a significant
innovation (not mine) which benefits both user and
author. For the user, there is the convenience of
leisurely evaluation of the program on your own system in
your own home or office to see if it's right for your
application. The low cost of user supported software
distribution also makes possible a voluntary payment
lower than the price of commercial software. And, of
course, there's no copy protection to diminish the
utility of the program.
For the author, there is the satisfaction of getting a
far larger number of copies into use than is possible
with conventional marketing. Also, the interference of
piracy is turned into a reinforcement in the distribution
of the program. Finally, if you have dealt with a
publisher, print or electronic, you understand the
undesirability of that alternative.
However, it doesn't work unless you want it to. If you
USER SUPPORTED SOFTWARE
find PC-DBMS useful, I hope you will register by making a
voluntary payment of $35, by check or through your bank
credit card. Beyond what you already have, this is what
you will receive from me.
- Support for a year. Comments, suggestions and
especially bug reports are welcome from anyone.
However, only registered users can expect
replies to their questions. Support will be
provided on a best efforts basis by mail and
through CompuServe. My CompuServe id is
74216,3033.
- Notices of subsequent PC-DBMS releases. Disks
and documentation will be available as indicated
below.
- My thanks. PC-DBMS took a year to develop, test
and document. I enjoyed every minute of it.
Now, I ask that you send your voluntary payment with the
order/payment form. A copy of that form is on the next
page or, if you wish to leave your manual intact, another
copy is in the file REQUEST.DOC. If you need an invoice,
you'll find one following the order/payment form in both
the manual and in REQUEST.DOC.
IF YOU REGISTER BY FEBRUARY 1, 1986, WE WILL SEND YOU A
FREE COPY OF THE TYPESET USER'S GUIDE.
For quantity purchases of PC-DBMS or to distribute it in
conjunction with your data base, contact Kware.
Whether or not you register, you are welcome to purchase
the typeset User's Guide ($10) or the
program/documentation disk ($10) or both ($18).
Purchasing materials is one way to support the continued
development of PC-DBMS and to keep yourself supplied with
the most current version.
C-2
USER SUPPORTED SOFTWARE
One way to keep current is to pre-order the next release.
It will be sent to you as soon as it's issued. You'll
get it faster and more reliably than through bulletin
boards and user groups. Just mark "next release" on the
applicable line of the order/payment form.
To register or to order materials, enter your name and
address on the form below and indicate the quantities
desired of the various items in the quantity column. Pay
by check or complete the credit card form.
C-3
USER SUPPORTED SOFTWARE
ORDER/PAYMENT FORM
Name _____________________________ Phone ____________
Company _____________________________
Address _____________________________
_____________________________
City _________________ State ________ Zip _________
Description Qty Price Extension
----------- --- ----- ---------
registration $35.00
User's Guide 10.00
PC-DBMS disk 10.00
Guide & disk 18.00
---------
Sub-total
Tax @ 4% (Va. residents only)
---------
Amount due
Kware Please make check payable
P.O. Box 16206 to Kware or complete
Arlington, Va. 22215 credit card form below.
Visa __ or Mastercard __ card # ___________________
exp mo/yr ______/______ signature ____________________
C-4
USER SUPPORTED SOFTWARE
INVOICE
Vendor: Kware, Inc. Federal EIN: 54-1304150
P. O. Box 16206
Arlington, Va. 22215 Date: / /
Description Qty Price Extension
----------- --- ----- ---------
PC-DBMS, $35.00
reg & support
User's Guide 10.00
PC-DBMS disk 10.00
User's Guide 18.00
& disk
---------
Sub-total
Tax @ 4% (Va. residents only)
---------
Amount due
You may retain this invoice.
C-5
INDEX
All
keyword . . . . . . . . . 3-5, 5-5, 5-11
Allf
keyword . . . . . . . . . 5-11
Alphabetize . . . . . . . . 3-6, 5-15
And
keyword . . . . . . . . . 3-6, 5-5, 5-12, 5-14 to 5-15
Apostrophe
comment delimiter . . . . 4-5
Ascii . . . . . . . . . . . 4-2, 5-13
Backslash
in deleted records . . . . 4-2, 5-3
Backspace key . . . . . . . 3-4, 3-6, 3-13, 4-4
Blank padding . . . . . . . 5-13
Brackets . . . . . . . . . . 5-2
Carriage return . . . . . . 3-4, 4-4
Case
sensitivity . . . . . . . 4-3, 5-2, A-1
Character string . . . . . . 4-5
see also text string
Close command . . . . . . . 5-9
Color . . . . . . . . . . . 2-1, 3-2
Command window . . . . . . . 3-4, 4-3, 4-5, 5-6
Comments
in commands . . . . . . . 4-5
Comparison . . . . . . . . . 5-12 to 5-14, 5-20
see also condition
Compuserve . . . . . . . . . C-2
Condition . . . . . . . . . 3-5, 5-12, 5-21
see also comparison
Continuation character . . . 4-4
Control-num lock . . . . . . 5-16, 5-20
Control-s . . . . . . . . . 3-7, 3-9, 5-16, 5-20
Cursor keys . . . . . . . . 3-4, 3-11, 3-13, 4-4
Data dictionary . . . . . . 3-10
see also schema
Default table . . . . . . . 3-7 to 3-8, 5-7, 5-11, 5-13
Index-1
Define command . . . . . . . 3-12, 5-2
Define utility . . . . . . . 3-12, 3-15, 4-2, 5-2
Dekey command . . . . . . . 3-15 to 3-16, 5-4
Delete key . . . . . . . . . 3-6, 4-4
Directory . . . . . . . . . 4-1, 5-16
see also subdirectory
Display command . . . . . . 3-10, 5-4, 5-7, 5-9
Display utility . . . . . . 3-10 to 3-11, 3-15, 4-3,
5-3, 5-5 to 5-8
Distribution diskette . . . 2-1
Dos . . . . . . . . . . . . 2-1, 2-3, 3-2, 3-16, 5-3
Drive names . . . . . . . . 5-3
Editor . . . . . . . . . . . 4-3, 5-8
Ellipses . . . . . . . . . . 5-2
End key . . . . . . . . . . 4-4
Errors
see messages
Exit command . . . . . . . . 3-16, 4-5, 5-8
Export
data . . . . . . . . . . . 4-2
F1 key . . . . . . . . . . . 3-3, 3-11, 4-5, 5-8
F10 key . . . . . . . . . . 3-7, 3-11 to 3-12,
3-15 to 3-16, 4-5, 5-3,
5-5 to 5-7, 5-16, 5-20
F2 key . . . . . . . . . . . 4-6
F3 key . . . . . . . . . . . 3-4, 4-5
F4 key . . . . . . . . . . . 3-12, 5-6
F6 key . . . . . . . . . . . 3-12, 5-6
F7 key . . . . . . . . . . . 3-4, 3-11 to 3-12, 4-5,
5-5 to 5-6
F9 key . . . . . . . . . . . 3-11, 4-5, 5-8
Field block . . . . . . . . 3-10 to 3-11, 4-3, 5-4, 5-8
Fields
filler . . . . . . . . . . 3-9, 5-3, 5-13, 5-15
functionally dependent . . B-3 to B-6
numeric . . . . . . . . . 3-9, 5-3
text . . . . . . . . . . . 3-9, 5-3
File specifications . . . . 5-3
Files
character positions in . . 3-9
data . . . . . . . . . . . 4-1 to 4-2, 5-2, 5-16, 5-19
def . . . . . . . . . . . 4-1
distribution . . . . . . . 2-1, C-1
Index-2
idx . . . . . . . . . . . 4-1
index . . . . . . . . . . 4-1, 5-4, 5-8, 5-10
maintenance . . . . . . . 3-15
schema . . . . . . . . . . 4-1 to 4-2, 5-9
Flying reform
editor . . . . . . . . . . 4-3 to 4-4
Functionally dependent
see fields
Help . . . . . . . . . . . . 3-3, 3-11, 4-5 to 4-6, 5-8
Help menu . . . . . . . . . 3-3
Home key . . . . . . . . . . 4-4
Import
data . . . . . . . . . . . 4-2, 5-3
Index . . . . . . . . . . . 3-15 to 3-16, 5-4,
5-8 to 5-9, 5-16
see also key
Insert mode
editor . . . . . . . . . . 4-3
Installation . . . . . . . . 2-2
Into clause . . . . . . . . 3-7 to 3-8, 3-15, 5-10, 5-16
Invoice . . . . . . . . . . C-5
Join
relational operation . . . 3-7 to 3-8, 3-16,
5-13 to 5-14, B-2
Key . . . . . . . . . . . . 3-15 to 3-16, 5-19
see also index
Key command . . . . . . . . 3-15, 5-8
Keyword . . . . . . . . . . 4-3, A-1
see also truncation
Main screen . . . . . . . . 3-2, 3-11 to 3-13, 3-15,
4-3, 4-5, 5-5 to 5-6
Menu
see help menu
Message window . . . . . . . 3-4
Messages
error or warning . . . . . 3-4, 4-5
Names
data base . . . . . . . . 4-3, A-1
field . . . . . . . . . . 4-3, A-1
Index-3
qualified field . . . . . 3-7, 4-3, 5-4, 5-9, 5-11,
5-13
table . . . . . . . . . . 4-3, A-1
into clause . . . . . . 5-16
Normal form . . . . . . . . B-2 to B-3, B-5 to B-6
Not
keyword . . . . . . . . . 5-14 to 5-15, 5-21
Numbers
representation . . . . . . 4-4
Open command . . . . . . . . 3-4, 5-9
Or
keyword . . . . . . . . . 3-6, 5-12, 5-14 to 5-15
Output command . . . . . . . 5-9 to 5-10
Output window . . . . . . . 3-2
Parentheses . . . . . . . . 5-14
Path names . . . . . . . . . 5-3
Primary key . . . . . . . . B-2 to B-6
Printer . . . . . . . . . . 5-9
Qualified field names
see names
Quote mark
text delimiter . . . . . . 3-6, 4-5, 5-7
Recall
command . . . . . . . . . 3-8
Record terminator . . . . . 5-3, 5-18
Relational model . . . . . . B-1
Remove command . . . . . . . 3-10, 4-2, 5-10
Replace mode
editor . . . . . . . . . . 4-3 to 4-4, 5-8
Schema . . . . . . . . . . . 3-10, 3-12, 4-1 to 4-2, 5-4,
5-8 to 5-10, 5-16 to 5-17
Scroll . . . . . . . . . . . 3-6 to 3-7, 3-9, 5-20
Select command . . . . . . . 3-5, 3-7 to 3-8, 4-3, 5-3,
5-5, 5-9 to 5-11
Show command . . . . . . . . 3-8, 3-10, 5-9, 5-17
Sort clause . . . . . . . . 3-6, 5-3, 5-15
maximum width . . . . . . 5-16
Space . . . . . . . . . . . 4-4, 5-13 to 5-14
Subdirectory . . . . . . . . 2-3, 3-2
see also directory
Index-4
Support . . . . . . . . . . C-2
Tab key . . . . . . . . . . 3-11, 5-8
Text string . . . . . . . . 3-6
see also character string
Tolerance command . . . . . 5-13, 5-20
Truncation
keyword . . . . . . . . . 3-8, 4-5, 5-2, A-1
User supported software . . C-1
Warnings
see messages
Welcome screen . . . . . . . 3-2
Where clause . . . . . . . . 3-5 to 3-6, 3-16, 5-3, 5-5,
5-7, 5-12 to 5-13
order of evaluation . . . 5-15
Word wrapping
editor . . . . . . . . . . 3-4, 4-3
Index-5
